﻿<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<!-- saved from url=(0039)http://skyshore.gicp.net/share/sql.html -->
<!-- 


--><HTML><HEAD><TITLE>SQL indent for B1</TITLE>
<META http-equiv=Content-Type content="text/html; charset=utf-8">
<META content="Liang, Jian - B1 IBD China" name=author>
<META content="B1 sql indent format profiler" name=keywords>
<META content="a tool to indent B1 sql query captured from SQL server profiler." 
name=description>
<STYLE type=text/css>BODY {
	FONT-SIZE: 10pt
}
TEXTAREA {
	FONT-SIZE: 10pt
}
UL {
	FONT-SIZE: 10pt
}
LI {
	FONT-SIZE: 10pt
}
DIV {
	FONT-SIZE: 10pt
}
FIELDSET {
	FONT-SIZE: 10pt
}
LEGEND {
	FONT-WEIGHT: bold
}
UL {
	PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; MARGIN: 10px; PADDING-TOP: 0px
}
LI {
	PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; MARGIN: 10px; PADDING-TOP: 0px
}
P {
	MARGIN-TOP: 5px; MARGIN-BOTTOM: 5px; MARGIN-LEFT: 40px; TEXT-INDENT: -20px
}
BUTTON {
	WIDTH: 150px
}
TD {
	VERTICAL-ALIGN: top
}
LI {
	CURSOR: pointer
}
FONT.match {
	DISPLAY: inline
}
.hl {
	BACKGROUND-COLOR: yellow
}
.key {
	FONT-WEIGHT: bold; COLOR: green
}
.key2 {
	COLOR: green
}
.tbl {
	FONT-WEIGHT: bold; TEXT-DECORATION: underline
}
.param {
	FONT-WEIGHT: bold; CURSOR: pointer; COLOR: blue
}
</STYLE>

<SCRIPT language=javascript>
// ========  {{{
var KEYS = "INSERT|UPDATE|DELETE|SELECT|FROM|INNER|LEFT|RIGHT|WHERE|GROUP|ORDER|UNION|VALUES|SET|FOR|GO|WITH|Declare|else if|if|else|begin|end|CREATE|HAVING|exec|INTO"; // used to split lines
var KEYS2 = "and|or|by|sp_executesql|outer|join|on|min|max|sum|count|avg|convert|datepart|top|not|in|all|IS|NULL|sp_prepexec|declare|exists|off|dateadd|datediff|getdate|as";
//}}}

// ======= toolkit {{{
function gi(id)
{return document.getElementById(id);}

function each(a, fn)
{
	for (var i=0; i<a.length; ++i) {
		fn(i,a[i]);
	}
}

function map(a, fn)
{
	var a1 = [];
	for (var i=0; i<a.length; ++i) {
		a1.push(fn(i,a[i]))
	}
	return a1;
}

function gn(name, fn)
{
	var a = document.getElementsByName(name);
	if (fn)
		each(a, fn);
	return a;
}

function gtn(tagname, fn)
{
	var a = document.getElementsByTagName(tagname);
	if (fn)
		each(a, fn);
	return a;
}

function rep(cnt, s)
{
	if (s==null) 
		s = "\t";
	return cnt<=0? "": (new Array(cnt+1).join(s));
}

function show(o, is_show)
{
	if (typeof o == 'string')
		o = gi(o);
	o.style.display = is_show? "": "none";
}
// }}}

// ======= function {{{
function setMatchBracket()
{
	gtn("font", function (i, o) {
		if (o.className == "match") {
			o.onmouseover = function(e) {
				this.className = "match hl";

				if (window.event) {
					e = window.event;
					e.cancelBubble=true; 
				}
				else {
					e.stopPropagation();
				}
			}
			o.onmouseout = function(e) {
				this.className = "match";

				if (window.event) {
					e = window.event;
					e.cancelBubble=true; 
				}
				else {
					e.stopPropagation();
				}
			}
		}
	});
}

// [sql] = txt_sql.value
function addHistory(sql)
{
	if (sql == null)
		sql = txt_sql.value;

	// add history
	if (lst_his.sel == null || lst_his.sel.data != sql) {
		var li = document.createElement("li");
		var text = sql.replace(/\r|\n|\b\[?dbo\]?\.|\[|\]/g, " ");
		var m = text.match(/(SELECT|DELETE|INSERT|UPDATE).*?(FROM|INTO)\s*(\w+)/i);
		if (m) {
				text = m[1] + " " + m[2] + " " + m[3];
		}
		else {
			text = text.substr(0, 30);
		}
		li.innerHTML = text + "...";
		li.data = sql;
		li.onclick = function () {
			if (lst_his.sel != this) {
				if (lst_his.sel != null)
					lst_his.sel.className = "";
				this.className = "hl";

				lst_his.sel = this;
			}
			if (txt_sql.value != this.data) {
				txt_sql.value = this.data;
				doFmt();
			}
		}
		li.ondblclick = function () {
			this.onclick();
			var text = prompt("new text for this item: ", this.innerHTML);
			if (text)
				this.innerHTML = text;
		}
		lst_his.insertBefore(li, lst_his.firstChild);
		li.onclick();
		li = null;
	}
}

function doFmt()
{
	// remove comments and to one line
	var sql = txt_sql.value.replace(/--.*$/mg, "")  // BUG_3
					.replace(/\s+/g, " ")
					.replace(/&gt;/g, ">")
					.replace(/&lt;/g, "<")
					.replace(/&quot;/g, "\"")
					.replace(/&apos;/g, "'")
					;

	var opt = {
		replace_param: gi('chk_param').checked,
		ind_by_bracket: gi('rdo_ind_bracket').checked,
		line_width: parseInt(gi('txt_linewidth').value),
		auto_match_bracket: gi('chk_automatch').checked,
		to_plain_text: gi('chk_plaintext').checked,
		keep_tab: gi('chk_keeptab').checked,
		max_cond_aline: parseInt(gi('txt_max_cond_aline').value)
	};

	// support multi queries in quoto, split statements by 'go' or ';', and concat the result.
	var result = map(sql.split(/\bgo\b/i), function (sql_idx, sql) {

		// parse params
		var params = [];
		var idx = 0;
		// process quotes of 2 types. don't process multi sql queries.
		if (sql.search(/^\s*exec\s+sp_executesql\s+N'(.*?)',N'(.*?)',(.*?)( go |$)/i) >= 0 ||
			(sql.search(/exec\s+sp_prepexec.*?N'(.*?)',N'(.*?)',(.*?)(select.*)?$/i) >= 0 && (idx=1)))
		{
			sql = idx==0? RegExp.$1: RegExp.$2;
			var param_def = idx==0? RegExp.$2: RegExp.$1;
			var param_val = RegExp.$3;

			param_def = param_def.match(/@\w+/g);
			param_val = param_val.split(',');

			for (var i=0; i<param_def.length; ++i) {
				params[param_def[i]] = param_val[i];
			}
		}

		// indent
		var level = 0;
		var bracket = 0;
		var blocklevel = 0;

		var stack = [0];
		var cnt = 0;
		var lastkey;
		sql = sql.replace(new RegExp("\\b(" + KEYS + ")\\b|\\b(and|or)\\b|\\(|\\)", "gi"), 
			function (m, m1, m2) {
				if (m == '(') {
					++ bracket;
					return m;
				}
				if (m == ')') {
					-- bracket;
					return m;
				}
				var key = m.toUpperCase();
				if (key == "BEGIN") {
					++ blocklevel;
					cnt = 0;
					lastkey = key;
					return m;
				}
				else if (key == "END")
					-- blocklevel;

				// process "SELECT ... INTO", dont process "INSERT INTO ..."
				if (key == "INTO" && lastkey == "INSERT") {
					lastkey = key;
					return m;
				}

				var indent = bracket + blocklevel;
				if (! opt.ind_by_bracket) {
					// start and end sub query
					if (key == "SELECT" && bracket > 0) {
						++ level;
						stack.push(bracket);
					}
					else if (bracket < stack[stack.length-1]) {
						-- level;
						stack.pop(bracket);
					}
					indent = level;
				}
				// add break if it is the top-level SELECT or a seperate SQL
				var linebreak = cnt>0 && ((indent-blocklevel==0 && !/UNION|EXEC|INTO/.test(lastkey) && /SELECT|INSERT|DELETE|UPDATE/.test(key)) || key == "UNION" || key == "EXEC")? "\n\n": "\n";
				++ cnt;
				lastkey = key;

				// "and", "or" -> indent+1
				// in firefox js, m2="" if not matched!!!
				return linebreak +rep(m2? indent+1: indent)+m;
			});

		// process param: (@P1) or P1, but not "(@P1" or "@P1)" or "in (@P1)" - BUG_2
		sql = sql.replace(/(in\s*)\((@\w+)\)/gi, "$1($2 )").replace(/\((@\w+)\)|@\w+/g, function (m, m_1) {
			var m1 = m_1 || m;
			if (params[m1] === undefined) // BUG_1: dont touch B1 user-defined fields like [@tbl]
				return m1;
			if (opt.to_plain_text) {
				if (opt.replace_param)
					return params[m1];
				else
					return m1;
			}
			else {
				if (opt.replace_param)
					return '<font class=param title="' + m1 + '">' + params[m1] + '</font>'
				else
					return '<font class=param title="' + params[m1] + '">' + m1 + '</font>'
			}
		});

		// group some sub-cond into one line, e.g. "a=1 \n\t or a=2" -> "a=1 or a=2"
		// NOTE: dont merge lines containing bracket open and close, like these:
		// 1. "a=1 \n\t or (a=2 ..."
		// 2. "... a=1) \n\t or a=2 "
		if (opt.max_cond_aline > 1) {
			var re = "((\\n\\t+)(and|or) +[^(].*$)";
			if (opt.max_cond_aline > 2)
				re += rep(opt.max_cond_aline-2, "(\\3(and|or) +[^(].*$)?");
			re = new RegExp("([)]\\s*$\\n?)|" + re + "\\n?", "mgi");
			sql = sql.replace(re, function(m, m1) {
				if (m1)
					return m1;
				return m.replace(/(\t|\n)+/g, "") + "\n";
			});

			// Horrible RE! Here is a simple explanation:
			// sample (if max_cond=3): / ( \)$ \n? )  |  ((\n\t+)(and|or) +[^(].*$)  (\3(and|or) +[^(].*$)?\n? /mgi
			// 1. core logic: /...|(\n\t+)(and|or).*$ \3(and|or).*$/  
			//    -- merge lines by remove the "\n\t" part. "\3"=(\n\t+), used to ensure all of the cond have the same indent level. 
			// 2. the pending "\n?"    
			//    -- eat the "\n" to destroy the continue match of the next line; "?" consider the end of sql string without "\n".
			// 3. the core logic of first part: /)$\n?|.../    
			//    -- eat the "\n" to destroy this match: "... a=1) \n\t or a=2...". the "m1" is for this part, just keep it and return.
		}
		
		// fix long date, e.g. '2010-01-01 00:00:00:000'  -> '2010-01-01'
		sql = sql.replace(/\s+00:00:00:000/g, "");

		// fix '' issue, e.g. ''2010-01-01 00:00:00:000''  -> '2010-01-01 00:00:00:000', Count(''A'') -> Count('A'), '''' -> ''
		// but dont change N'' to N'
		sql = sql.replace(/''([0-9a-z: -]*?)''/gi, "'$1'");
		//sql = sql.replace(/''/gi, "'");

		// fields info
		if (sql_idx == 0) {
			var s = '';
			if (sql.search(/insert.*?\((.+?)\)\s*values\s*\((.*?)\)/i) > 0) {
				var keys = RegExp.$1;
				var values = RegExp.$2;
				keys = keys.split(",");
				values = values.split(",");
				each(keys, function (i, e) {
					s += (i+1) + ". " + e + " => " + values[i] + "<br/>";
				});
			}
			else if (sql.search(/select\s+(.+?)\s+from/i) > 0) {
				var keys = RegExp.$1;
				keys = keys.split(",");
				each(keys, function (i, e) {
					s += (i+1) + ". " + e + "<br/>";
				});
			}
			gi('div_fields').innerHTML = s;
		}

		// indent long line 
		if (opt.line_width > 0) {
			sql = sql.replace(new RegExp("^(\\t*)((SELECT|FROM|GROUP|ORDER).{" + opt.line_width + ",})$", "gim"), function (m,m1,m2) {
				var indent = m1.length;
				// !!! should use "\G" in regexp like perl. but js don't support \G. so if line_width is little, the match has bug.
				var arr;
				if (arr = m2.match(new RegExp(".{1," + opt.line_width + "}(,\\s*|$)", "g"))) {
					m2 = arr.join("\n"+rep(indent+1));
				}
				return m1+m2;
			});
		}

		// tables. e.g. [dbo].[ojdt] -> [ojdt]
		//sql = sql.replace(/(FROM|JOIN|INTO)\s+(\S+)/gi, "$1 <b>$2</b>");
		var tables = [], tbl_cnt = {};
		sql = sql.replace(/(FROM|JOIN|INTO)\s+(?:\[?dbo\]?\.)?([^ \t();]+)/gi, function (m, m1, m2) {
			var tbl = m2.toLowerCase();
			if (tbl_cnt[tbl] != null)
				++ tbl_cnt[tbl];
			else {
				tbl_cnt[tbl] = 1;
				tables.push(m2);
			}
			return opt.to_plain_text? m1 + " " + m2: m1 + " <font class=tbl>" + m2 + "</font>";
		});

		// table info
		if (sql_idx == 0) {
			var s = '';
			each(tables, function(i, e) {
				if (s == '')
					s = e;
				else
					s += ", " + e;
				if (tbl_cnt[e.toLowerCase()] != 1) {
					s += "*" + tbl_cnt[e.toLowerCase()];
				}
			});
			gi('div_tbls').innerHTML = s;
		}

		return sql;
	}).join("\n\n");

	// output result
	// to html
	if (! opt.to_plain_text) {
		// highlight keys
		result = result.replace(new RegExp("\\b(" + KEYS + ")\\b", "gi"), "<font class=key>$&</font>")
				// highlight other keys (key2)
				.replace(new RegExp("\\b(" + KEYS2 + ")\\b", "gi"), "<font class=key2>$&</font>")
				// support multi-sql
				.replace(/\n\n/g, "\n&nbsp;\n")
				// replace indent to html
				.replace(/^(\t*)(.+?)$/gm, function (m,m1,m2) {
					//var ret = "<p style='padding-left: " + 40*m1.length + "px;'>" + m2 + "</p>\n";
					var ret = "<p>" + rep(4*m1.length,"&nbsp;") + m2 + "</p>\n";
					return ret;
				})

		if (opt.auto_match_bracket) {
			result = result.replace(/\(/g, "<font class=match>(")
				.replace(/\)/g, ")</font>");
		}
	}
	if (opt.to_plain_text) {
		if (! opt.keep_tab)
			result = result.replace(/\t/g, '    ');
		gi('result').innerHTML = "<textarea cols=100 rows=40 wrap=off>" + result + "</textarea>";
	}
	else {
		gi('result').innerHTML = result;

		// brackets auto match (only for IE)
		if (opt.auto_match_bracket)
			setMatchBracket();
	}

//	addHistory(sql0);
}
// }}}

// ======= main {{{
var lst_his;
var txt_sql;

window.onload = function () {
	lst_his = gi('lst_his');
	txt_sql = gi('txt_sql');

	doFmt();
	addHistory();

	// solve IE copy problem: if you copy lines in IE, black line is inserted between <p>
	document.body.oncopy = function() {
		event.returnValue=false;
		var t=document.selection.createRange().text;
		clipboardData.setData('Text', t);
	};
	
}

function copyResult()
{
	if (window.clipboardData) {
		window.clipboardData.setData('Text',gi('result').innerText)
		alert("OK!");
		return;
	}
	alert("Your explorer may not support clipboard. Please tick the option \"output plain text\" for manully copying.");
}
// }}}
</SCRIPT>

<META content="MSHTML 6.00.2900.6104" name=GENERATOR></HEAD>
<BODY><B>SQL indent for B1</B> - last update: 2011/7/27 
<HR>

<TABLE width="100%">
  <TBODY>
  <TR>
    <TD>
      <FIELDSET><LEGEND>Copy SQL here from profiler</LEGEND>
      <TABLE>
        <TBODY>
        <TR>
          <TD><TEXTAREA id=txt_sql onkeyup=doFmt() onfocus=this.select() name="" rows=10 cols=80 onchange=doFmt()>
</TEXTAREA> 
</TD>
          <TD><BUTTON id=btn_go onclick=doFmt();><B>indent 
            SQL</B></BUTTON><BR><BUTTON id=btn_his onclick=addHistory();>add to 
            history</BUTTON><BR><BUTTON onclick=copyResult()>copy 
            result</BUTTON><BR>
      </TD></TR></TBODY></TABLE></FIELDSET> 
      <FIELDSET><LEGEND>Result</LEGEND>
      <DIV id=result style="MIN-HEIGHT: 500px">(OUTPUT)</DIV></FIELDSET> </TD>
    <TD style="WIDTH: 240px; min-width: 240px">
      <FIELDSET><LEGEND>Options</LEGEND>line width: <INPUT id=txt_linewidth 
      onchange=doFmt() size=4 value=100>chars <BR>(for SELECT/GROUP BY/ORDER 
      BY)<BR><BR>max conditions per line: <INPUT id=txt_max_cond_aline 
      onchange=doFmt() size=1 value=2> <BR><BR>indent by: <BR><INPUT 
      id=rdo_ind_bracket onclick=doFmt() type=radio CHECKED name=rdo_ind><LABEL 
      for=rdo_ind_bracket>bracket</LABEL><BR><INPUT id=rdo_ind_query 
      onclick=doFmt() type=radio name=rdo_ind><LABEL 
      for=rdo_ind_query>sub-query</LABEL> <BR><BR>other opt:<BR><INPUT 
      id=chk_param onclick=doFmt() type=checkbox CHECKED><LABEL 
      for=chk_param>replace parameters</LABEL> <BR><INPUT id=chk_plaintext 
      onclick="show('div_keeptab', this.checked); show('div_automatch', !this.checked); doFmt();" 
      type=checkbox><LABEL for=chk_plaintext>output plain text</LABEL><BR><SPAN 
      id=div_keeptab style="DISPLAY: none; PADDING-LEFT: 20px"><INPUT 
      id=chk_keeptab onclick=doFmt() type=checkbox><LABEL for=chk_keeptab>keep 
      tab</LABEL><BR></SPAN><SPAN id=div_automatch><INPUT id=chk_automatch 
      onclick=doFmt() type=checkbox><LABEL for=chk_automatch>brackets auto match 
      (IE)</LABEL><BR></SPAN></FIELDSET> 
      <FIELDSET><LEGEND>History</LEGEND>
      <UL id=lst_his></UL></FIELDSET> 
      <FIELDSET><LEGEND>Extra info</LEGEND>
      <DIV style="MARGIN: 5px"><B>Tables:</B> 
      <DIV id=div_tbls></DIV><BR><B>Fields: (SELECT/INSERT)</B> 
      <DIV id=div_fields></DIV><BR> 
      </DIV></FIELDSET> </TD></TR></TBODY></TABLE><!-- vim: set foldmethod=marker : --></BODY></HTML>
